package orest.back.market;

import java.sql.Connection;
import java.sql.Date;

import net.ssta.core.base.SQLBuilderBase;
import net.ssta.core.web.RequestKit;



/**
 * 
 * @ClassName:
 * @Description: 
 * @author: 
 * @date: 
 * 
 */
public class MarketSql extends SQLBuilderBase
{
    /*
     * m_builder : 单例实例
     */
    private static MarketSql m_builder = new MarketSql();
    
    /**
     * @Title: getSQLBuilder
     * @Description: 获取实例
     * 
     * @param conn 数据库连
     * @return 单例实例
     */
    public static MarketSql getSQLBuilder(Connection conn)
    {
        return m_builder;
    }
    
    /**
     * 用户项目需求列表sql语句
     * 
     * @param RequestKit
     * @return
     */
    public static String getTechneedListSql(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.*,U.LOGINNAME,U.FULLNAME FROM T_TECHNEED T,V_REGISTER_USER U WHERE 1=1 AND T.USERID=U.USERID ");
        
        //关键词，与标题、关键字匹配,keyword修正为发布人
        String keyword = kit.getParameter("keyword","");
        if(!keyword.equals("")){
        	sb.append(" AND (U.LOGINNAME LIKE '%").append(keyword).append("%' ");
        	sb.append(" OR U.FULLNAME LIKE '%").append(keyword).append("%') ");
        }
        
        //提交日期
        Date today = new Date(System.currentTimeMillis());
        String datetype = kit.getParameter("SUBMITDATE", "");
        if("1".equals(datetype)){
        	sb.append(" AND T.SUBMITDATE='").append(today).append("' ");
        }else if("2".equals(datetype)){
        	String weekday = MarketHandler.getCalculateDate(0, 0, -7);
        	sb.append(" AND T.SUBMITDATE>='").append(weekday).append("' ");
        }else if("3".equals(datetype)){
        	String monthday = MarketHandler.getCalculateDate(0, -1, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(monthday).append("' ");
        }else if("4".equals(datetype)){
        	String halfyearday = MarketHandler.getCalculateDate(0, -6, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(halfyearday).append("' ");
        }
        
        /*
        if(!keyword.equals("")){
        	sb.append(" AND (T.TITLE LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        }
        */
        
        //所属行业分类
        /*
        String newenergy = kit.getParameter("NEWENERGYCLASS","");
        if(newenergy!=null&&!newenergy.equals("")){
        	sb.append(" AND T.NEWENERGYCLASS = '").append(newenergy).append("' ");
        }
        */
        
        //合作方式
        /*
        String cooperateway = kit.getParameter("COOPERATEWAY","");
        if(cooperateway!=null&&!cooperateway.equals("")){
        	sb.append(" AND T.COOPERATEWAY = '").append(cooperateway).append("' ");
        }
        */
        
        //审核状态
        String ifcheck = kit.getParameter("IFCHECK","");
        if(ifcheck!=null&&!ifcheck.equals("")){
        	sb.append(" AND T.IFCHECK = '").append(ifcheck).append("' ");
        }
        
        //是否推荐
        String homepageshow = kit.getParameter("HOMEPAGESHOW", "");
        if(homepageshow!=null && !"".equals(homepageshow)){
        	sb.append(" AND T.HOMEPAGESHOW = '").append(homepageshow).append("' ");
        }
        
        sb.append(" ORDER BY SUBMITDATE DESC");
        //System.out.println(sb.toString());
        return sb.toString();
    }
    
    /**
     * 根据技术需求uuid获取详细信息
     * 
     * @param uuid:技术需求的唯一标识
     * 
     * @return：sql字符串
     */
    public static String getTechneedInfoSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM T_TECHNEED WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 更改技术需求的审批状态sql
     * 
     * @param uuid:技术需求的唯一标识
     * @param ifcheck:技术需求的审批状态
     * 
     * @return：更改审批状态sql字符串
     */
    public static String updateTechneedCheckSql(String uuid,String ifcheck)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_TECHNEED  SET IFCHECK='").append(ifcheck).append("' WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    /**
     * 更改技术需求的推荐状态sql
     * 
     * @param uuid:技术需求的唯一标识
     * @param recommend:技术需求的推荐状态
     * 
     * @return：更改推荐状态sql字符串
     */
    public static String updateTechneedRecommendSql(String uuid,String recommend)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_TECHNEED  SET HOMEPAGESHOW='").append(recommend).append("',CHANNELSHOW='").append(recommend).append("' WHERE ID ='").append(uuid).append("'");
        if("1".equals(recommend)){
        	//如果是取消推荐，则无需判断是否审核通过，为了过滤测试中的脏数据
        	sb.append(" AND IFCHECK='1'");
        }
        return sb.toString();
    }
    
    /**
     * 删除技术需求的sql
     * 
     * @param uuid:技术需求的唯一标识
     * 
     * @return：更改推荐状态sql字符串
     */
    public static String deleteTechneedSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_TECHNEED WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 用户自荐项目列表sql语句
     * 
     * @param RequestKit
     * @return
     */
    public static String getSelloneselfListSql(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.*,U.LOGINNAME,U.FULLNAME FROM T_SELLONESELF T,V_REGISTER_USER U WHERE 1=1 AND T.USERID=U.USERID ");
        
      //关键词，与标题、关键字匹配,keyword修正为发布人
        String keyword = kit.getParameter("keyword","");
        if(!keyword.equals("")){
        	sb.append(" AND (U.LOGINNAME LIKE '%").append(keyword).append("%' ");
        	sb.append(" OR U.FULLNAME LIKE '%").append(keyword).append("%') ");
        }
        
        //提交日期
        Date today = new Date(System.currentTimeMillis());
        String datetype = kit.getParameter("SUBMITDATE", "");
        if("1".equals(datetype)){
        	sb.append(" AND T.SUBMITDATE='").append(today).append("' ");
        }else if("2".equals(datetype)){
        	String weekday = MarketHandler.getCalculateDate(0, 0, -7);
        	sb.append(" AND T.SUBMITDATE>='").append(weekday).append("' ");
        }else if("3".equals(datetype)){
        	String monthday = MarketHandler.getCalculateDate(0, -1, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(monthday).append("' ");
        }else if("4".equals(datetype)){
        	String halfyearday = MarketHandler.getCalculateDate(0, -6, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(halfyearday).append("' ");
        }
        
        /*
        if(!keyword.equals("")){
        	sb.append(" AND (T.TITLE LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        }
        */
        
        //所属行业分类
        /*
        String newenergy = kit.getParameter("NEWENERGYCLASS","");
        if(newenergy!=null&&!newenergy.equals("")){
        	sb.append(" AND T.NEWENERGYCLASS = '").append(newenergy).append("' ");
        }
        */
        //合作方式
        /*
        String cooperateway = kit.getParameter("COOPERATEWAY","");
        if(cooperateway!=null&&!cooperateway.equals("")){
        	sb.append(" AND T.COOPERATEWAY = '").append(cooperateway).append("' ");
        }
        */
        
        //审核状态
        String ifcheck = kit.getParameter("IFCHECK","");
        if(ifcheck!=null&&!ifcheck.equals("")){
        	sb.append(" AND T.IFCHECK = '").append(ifcheck).append("' ");
        }
        
        //是否推荐
        String homepageshow = kit.getParameter("HOMEPAGESHOW", "");
        if(homepageshow!=null && !"".equals(homepageshow)){
        	sb.append(" AND T.HOMEPAGESHOW = '").append(homepageshow).append("' ");
        }
        
        
        sb.append(" ORDER BY SUBMITDATE DESC");
        System.out.println(sb.toString());
        return sb.toString();
    }
    /**
     * 根据自荐项目uuid获取详细信息
     * 
     * @param uuid:自荐项目的唯一标识
     * 
     * @return：sql字符串
     */
    public static String getSelloneselfInfoSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM T_SELLONESELF WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 更改自荐项目的审批状态sql
     * 
     * @param uuid:自荐项目的唯一标识
     * @param ifcheck:自荐项目的审批状态
     * 
     * @return：更改审批状态sql字符串
     */
    public static String updateSelloneselfCheckSql(String uuid,String ifcheck)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_SELLONESELF  SET IFCHECK='").append(ifcheck).append("' WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    /**
     * 更改自荐项目的推荐状态sql
     * 
     * @param uuid:自荐项目的唯一标识
     * @param recommend:自荐项目的推荐状态
     * 
     * @return：更改推荐状态sql字符串
     */
    public static String updateSelloneselfRecommendSql(String uuid,String recommend)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_SELLONESELF  SET HOMEPAGESHOW='").append(recommend).append("',CHANNELSHOW='").append(recommend).append("' WHERE ID ='").append(uuid).append("' ");
        
        if("1".equals(recommend)){
        	//如果是取消推荐，则无需判断是否审核通过，为了过滤测试中的脏数据
        	sb.append(" AND IFCHECK='1'");
        }
        return sb.toString();
    }
    /**
     * 删除自荐项目信息的sql
     * 
     * @param uuid:自荐项目的唯一标识
     * 
     * @return：删除自荐项目sql字符串
     */
    public static String deleteSelloneselfSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_SELLONESELF WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 用户投融资项目列表sql语句
     * 
     * @param RequestKit
     * @return
     */
    public static String getInvestListSql(RequestKit kit)
    {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT T.*,U.LOGINNAME,U.FULLNAME FROM T_INVESTANDFINANCE T,V_REGISTER_USER U WHERE 1=1 AND T.USERID=U.USERID ");
        
      //关键词，与标题、关键字匹配,keyword修正为发布人
        String keyword = kit.getParameter("keyword","");
        if(!keyword.equals("")){
        	sb.append(" AND (U.LOGINNAME LIKE '%").append(keyword).append("%' ");
        	sb.append(" OR U.FULLNAME LIKE '%").append(keyword).append("%') ");
        }
        
        //提交日期
        Date today = new Date(System.currentTimeMillis());
        String datetype = kit.getParameter("SUBMITDATE", "");
        if("1".equals(datetype)){
        	sb.append(" AND T.SUBMITDATE='").append(today).append("' ");
        }else if("2".equals(datetype)){
        	String weekday = MarketHandler.getCalculateDate(0, 0, -7);
        	sb.append(" AND T.SUBMITDATE>='").append(weekday).append("' ");
        }else if("3".equals(datetype)){
        	String monthday = MarketHandler.getCalculateDate(0, -1, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(monthday).append("' ");
        }else if("4".equals(datetype)){
        	String halfyearday = MarketHandler.getCalculateDate(0, -6, 0);
        	sb.append(" AND T.SUBMITDATE>='").append(halfyearday).append("' ");
        }
        
        /*
        if(!keyword.equals("")){
        	sb.append(" AND (T.TITLE LIKE '%").append(kit.getParameter("keyword","")).append("%'");
        	sb.append(" OR T.TOPICWORDS LIKE '%").append(keyword).append("%') ");
        }
        */
        //所属行业分类
        /*
        String newenergy = kit.getParameter("NEWENERGYCLASS","");
        if(newenergy!=null&&!newenergy.equals("")){
        	sb.append(" AND T.NEWENERGYCLASS = '").append(newenergy).append("' ");
        }
        */
        
        //合作方式
        /*
        String cooperateway = kit.getParameter("COOPERATEWAY","");
        if(cooperateway!=null&&!cooperateway.equals("")){
        	sb.append(" AND T.COOPERATEWAY = '").append(cooperateway).append("' ");
        }*/
        
        //审核状态
        String ifcheck = kit.getParameter("IFCHECK","");
        if(ifcheck!=null&&!ifcheck.equals("")){
        	sb.append(" AND T.IFCHECK = '").append(ifcheck).append("' ");
        }
        
        //是否推荐
        String homepageshow = kit.getParameter("HOMEPAGESHOW", "");
        if(homepageshow!=null && !homepageshow.equals("")){
        	sb.append(" AND T.HOMEPAGESHOW = '").append(homepageshow).append("' ");
        }
        
        sb.append(" ORDER BY SUBMITDATE DESC");
        System.out.println(sb.toString());
        return sb.toString();
    }
    
    /**
     * 根据投融资项目uuid获取详细信息
     * 
     * @param uuid:投融资项目的唯一标识
     * 
     * @return：sql字符串
     */
    public static String getInvestInfoSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM T_INVESTANDFINANCE WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    
    /**
     * 更改投融资项目的审批状态sql
     * 
     * @param uuid:投融资项目的唯一标识
     * @param ifcheck:投融资项目的审批状态
     * 
     * @return：更改审批状态sql字符串
     */
    public static String updateInvestCheckSql(String uuid,String ifcheck)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_INVESTANDFINANCE  SET IFCHECK='").append(ifcheck).append("' WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
    /**
     * 更改投融资项目的推荐状态sql
     * 
     * @param uuid:投融资项目的唯一标识
     * @param recommend:投融资项目的推荐状态
     * 
     * @return：更改推荐状态sql字符串
     */
    public static String updateInvestRecommendSql(String uuid,String recommend)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" UPDATE T_INVESTANDFINANCE  SET HOMEPAGESHOW='").append(recommend).append("',CHANNELSHOW='").append(recommend).append("' WHERE ID ='").append(uuid).append("' ");
        if("1".equals(recommend)){
        	//如果是取消推荐，则无需判断是否审核通过，为了过滤测试中的脏数据
        	sb.append(" AND IFCHECK='1'");
        }
        return sb.toString();
    }
    
    /**
     * 删除投融资项目的sql
     * 
     * @param uuid:投融资项目的唯一标识
     * 
     * @return：删除投融资项目信息的sql字符串
     */
    public static String deleteInvestSql(String uuid)
    {
        StringBuffer sb = new StringBuffer();
        sb.append(" DELETE FROM T_INVESTANDFINANCE WHERE ID ='").append(uuid).append("' ");
        return sb.toString();
    }
}
